<?php

/**
 * Created by PhpStorm.
 * User: zuoa
 * Date: 2016/12/16 0016
 * Time: 14:08
 */
class Position_model extends Y_Model
{

    private $jjdd = array(
        30.4128800000, 120.2823860000, 30.4110530000, 120.2863930000
    );

    private $gafj = array(
        30.4142920000, 120.3025590000, 30.4122330000, 120.3068010000
    );


    public function add_record($r)
    {
        return $this->db->insert("tbl_position", $r);
    }

    public function is_in_area($lng, $lat)
    {
        if ($lng > $this->jjdd[1] && $lng < $this->jjdd[3] && $lat > $this->jjdd[2] && $lat < $this->jjdd[0]) {
            return "交警大队";
        } else if ($lng > $this->gafj[1] && $lng < $this->gafj[3] && $lat > $this->gafj[2] && $lat < $this->gafj[0]) {
            return "公安分局";
        } else {
            return '';
        }
    }

    public function get_today_first_record($openid)
    {
        $this->db->where("DATE(ts) = CURDATE()");
        $this->db->where("openid", $openid);
        $this->db->group_start()
            ->group_start()
            ->where("lng >", $this->jjdd[1])
            ->where("lng <", $this->jjdd[3])
            ->where("lat >", $this->jjdd[2])
            ->where("lat <", $this->jjdd[0])
            ->group_end()
            ->or_group_start()
            ->where("lng >", $this->gafj[1])
            ->where("lng <", $this->gafj[3])
            ->where("lat >", $this->gafj[2])
            ->where("lat <", $this->gafj[0])
            ->group_end()
            ->group_end();
        return $this->db->get("tbl_position")->first_row();
    }

    public function get_today_last_record($openid)
    {
        $this->db->where("DATE(ts) = CURDATE()");
        $this->db->where("openid", $openid);
        $this->db->group_start()
            ->group_start()
            ->where("lng >", $this->jjdd[1])
            ->where("lng <", $this->jjdd[3])
            ->where("lat >", $this->jjdd[2])
            ->where("lat <", $this->jjdd[0])
            ->group_end()
            ->or_group_start()
            ->where("lng >", $this->gafj[1])
            ->where("lng <", $this->gafj[3])
            ->where("lat >", $this->gafj[2])
            ->where("lat <", $this->gafj[0])
            ->group_end()
            ->group_end();
        return $this->db->get("tbl_position")->last_row();
    }


    public function get_month_export_simple($month)
    {
        $this->load->dbutil();

        $this->db->select("card as 职员代码, name as 职员, DATE_FORMAT(ts,'%Y%m%d') as 考勤日期, min(ts) as 第一段上班时间, max(ts) as 第一段下班时间");
        $this->db->where("DATE_FORMAT(ts, '%Y%m') = ", $month);
        $this->db->group_start()
            ->group_start()
            ->where("lng >", $this->jjdd[1])
            ->where("lng <", $this->jjdd[3])
            ->where("lat >", $this->jjdd[2])
            ->where("lat <", $this->jjdd[0])
            ->group_end()
            ->or_group_start()
            ->where("lng >", $this->gafj[1])
            ->where("lng <", $this->gafj[3])
            ->where("lat >", $this->gafj[2])
            ->where("lat <", $this->gafj[0])
            ->group_end()
            ->group_end();
        $this->db->group_by("DATE_FORMAT(ts,'%Y%m%d')");
        $this->db->group_by("name");
        $this->db->order_by("DATE_FORMAT(ts,'%Y%m%d') desc");
        $this->db->order_by("name desc");
        $query = $this->db->get("view_position");

        return $this->dbutil->csv_from_result($query);
    }

    public function get_month_export($month)
    {
        $this->load->dbutil();

        $this->db->select("name, ts, lng, lat");
        $this->db->where("DATE_FORMAT(ts, '%Y%m') = ", $month);
        $this->db->group_start()
            ->group_start()
            ->where("lng >", $this->jjdd[1])
            ->where("lng <", $this->jjdd[3])
            ->where("lat >", $this->jjdd[2])
            ->where("lat <", $this->jjdd[0])
            ->group_end()
            ->or_group_start()
            ->where("lng >", $this->gafj[1])
            ->where("lng <", $this->gafj[3])
            ->where("lat >", $this->gafj[2])
            ->where("lat <", $this->gafj[0])
            ->group_end()
            ->group_end();
        $this->db->order_by("name desc");
        $this->db->order_by("ts desc");
        $query = $this->db->get("view_position");

        return $this->dbutil->csv_from_result($query);
    }

    public function get_today_checkin()
    {
        $this->db->where("DATE(ts) = CURDATE()");
        $this->db->group_start()
            ->group_start()
            ->where("lng >", $this->jjdd[1])
            ->where("lng <", $this->jjdd[3])
            ->where("lat >", $this->jjdd[2])
            ->where("lat <", $this->jjdd[0])
            ->group_end()
            ->or_group_start()
            ->where("lng >", $this->gafj[1])
            ->where("lng <", $this->gafj[3])
            ->where("lat >", $this->gafj[2])
            ->where("lat <", $this->gafj[0])
            ->group_end()
            ->group_end();

        $this->db->order_by("ts asc");
        $this->db->group_by("name");

        return $this->db->get("view_position")->result_array();
    }
}